package com.hereway.core.util;

import java.io.File;
import java.io.FileOutputStream;
import java.sql.Connection;
import java.sql.DatabaseMetaData;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

public class MetadataUtil {
	private Connection cn = null;
	private String catalog = null;//SqlServer use
	private String schemaPattern = "AGENTSKY";//Oracle use
	
	public MetadataUtil()throws Exception{
		String driver = "oracle.jdbc.driver.OracleDriver";
   		String url = "jdbc:oracle:thin:@localhost:1521:CRM";
   		String uid = "agentsky";
   		String pwd = "agentsky";

   		Class.forName(driver);
	   	this.cn = DriverManager.getConnection(url,uid,pwd);
	}
	
	private String getTablePrimaryKeys(String tableName){
		try{
			DatabaseMetaData dbmd = cn.getMetaData();
			ResultSet rs = dbmd.getPrimaryKeys(catalog, schemaPattern, tableName);
			StringBuffer sb = new StringBuffer(",");
			while(rs.next()){
				sb.append(rs.getString("COLUMN_NAME") + ",");
			}
			rs.close();
			return sb.toString();
		}catch(Exception ex){
			return "";
		}
	}
	
	private boolean containFieldType(String fieldType){
		List types = new ArrayList();
		types.add("CHAR");
		types.add("NCHAR");
		types.add("NVARCHAR");
		types.add("VARCHAR");
		types.add("VARCHAR2");
		return types.contains(fieldType.toUpperCase());
	}
	
	/**
	 * 取得表的备注信息
	 */
	private Map<String, String> getTableComments()throws Exception{
		Map<String, String> colMap = new HashMap<String, String>();
		
		StringBuffer sb = new StringBuffer();
		sb.append("select TABLE_NAME,TABLE_TYPE,COMMENTS from user_tab_comments");
		
		PreparedStatement pstm = cn.prepareStatement(sb.toString());
		ResultSet rs = pstm.executeQuery();
		while(rs.next()){
			colMap.put(rs.getString("TABLE_NAME").toUpperCase(), rs.getString("COMMENTS"));
		}
	    rs.close();
	    pstm.close();
		
		return colMap;
	}
	
	/**
	 * 取得表字段的备注信息
	 */
	private Map<String, String> getColumnComments(String tableName)throws Exception{
		Map<String, String> colMap = new HashMap<String, String>();
		
		StringBuffer sb = new StringBuffer();
		sb.append(" select TABLE_NAME,COLUMN_NAME,COMMENTS from user_col_comments ");
		sb.append(" where upper(TABLE_NAME)=upper('" + tableName + "') ");
		
		PreparedStatement pstm = cn.prepareStatement(sb.toString());
		ResultSet rs = pstm.executeQuery();
		while(rs.next()){
			colMap.put(rs.getString("COLUMN_NAME").toUpperCase(), rs.getString("COMMENTS"));
		}
	    rs.close();
	    pstm.close();
		
		return colMap;
	}
	
//	public void createTableMetadata(String fileName){
//		try{
//			if(fileName == null || fileName.trim().length() == 0){
//				throw new IllegalArgumentException("argument fileName can not be null");
//			}
//			File file = new File(fileName);
//			
//			//delete old file
//			if(file.exists() && file.isFile()) file.delete();
//			
//			//create sheet
//			FileOutputStream out = new FileOutputStream(file);
//			WritableWorkbook book = Workbook.createWorkbook(out);
//			WritableSheet sheet = book.createSheet("数据字典",0);
//			
//			//表备注
//			Map<String, String> tableMap = getTableComments();
//			
//			DatabaseMetaData dbmd = cn.getMetaData();
//			String[] types = {"TABLE"};
//			ResultSet rs = dbmd.getTables(catalog ,schemaPattern, null, types);
//			int rowIndex = 0;
//			int tableCount = 0;
//			while(rs.next()){
//				try{
//					String tableName = rs.getString("TABLE_NAME");
//					if(tableName.indexOf("=")!=-1) continue;
//					
//					tableCount++;
//					System.out.println(tableCount + "、" + tableName + " doing...");
//					
//					//表字段备注信息
//					Map<String, String> colMap = getColumnComments(tableName);
//					
//					//表备注
//					String tableComment = tableMap.get(tableName);
//					if(CommonUtil.isNotEmpty(tableComment)){
//						tableComment = "：" + tableComment;
//					}else{
//						tableComment = CommonUtil.trim(tableComment);
//					}
//					
//					//表名
//					sheet.mergeCells(0,rowIndex,6,rowIndex);  //合并单元格，6数字要与表头的cell个数一致
//					sheet.addCell(new Label(0,rowIndex,tableName + tableComment));
//					rowIndex++;
//					
//					//表头
//					sheet.addCell(new Label(0,rowIndex,"序号"));
//					sheet.addCell(new Label(1,rowIndex,"字段名"));
//					sheet.addCell(new Label(2,rowIndex,"字段描述"));
//					sheet.addCell(new Label(3,rowIndex,"字段类型"));
//					sheet.addCell(new Label(4,rowIndex,"主键"));
//					sheet.addCell(new Label(5,rowIndex,"可空"));
//					sheet.addCell(new Label(6,rowIndex,"备注"));
//					rowIndex++;
//					
//					//主键
//					String strPrimaryKeys = getTablePrimaryKeys(tableName);
//					
//					Statement stm = cn.createStatement();
//					stm.setMaxRows(1);
//					ResultSet rsColumn = stm.executeQuery("select * from " + tableName);
//					ResultSetMetaData rsmd = rsColumn.getMetaData();
//					int recordIndex = 1;
//					for(int i=1;i<=rsmd.getColumnCount();i++){
//						sheet.addCell(new Label(0,rowIndex,String.valueOf(recordIndex))); //序号
//						sheet.addCell(new Label(1,rowIndex,rsmd.getColumnName(i))); //字段名
//						sheet.addCell(new Label(2,rowIndex,colMap.get(rsmd.getColumnName(i).toUpperCase()))); //描述
//						
//						//字段类型
//						String fieldType = rsmd.getColumnTypeName(i);
//						if(containFieldType(fieldType)){
//							fieldType += "(" + String.valueOf(rsmd.getColumnDisplaySize(i)) + ")";
//						}
//						sheet.addCell(new Label(3,rowIndex,fieldType));
//						
//						//是否主键
//						if(strPrimaryKeys.indexOf("," + rsmd.getColumnName(i) + ",") != -1){
//							sheet.addCell(new Label(4,rowIndex,"Y"));
//						}else{
//							sheet.addCell(new Label(4,rowIndex,""));
//						}
//						
//						//是否可空
//						sheet.addCell(new Label(5,rowIndex,(rsmd.isNullable(i)==1)?"":"N"));
//						
//						//备注
//						sheet.addCell(new Label(6,rowIndex,""));
//						
//						
//						rowIndex++;
//						recordIndex++;
//					}
//					rowIndex += 2;
//					
//					rsColumn.close();
//					stm.close();
//				}catch(Exception e){
//					e.printStackTrace();
//				}
//			}
//			rs.close();
//			
//			book.write();
//			book.close();
//		}catch(Exception ex){
//			ex.printStackTrace();
//		}finally{
//			try{
//				if(cn != null)cn.close();
//			}catch(Exception e){
//				e.printStackTrace();
//			}
//		}
//	}
//	
	public static void main(String[] args) {
		try{
			System.out.println("start...");
			MetadataUtil md = new MetadataUtil();
			//md.createTableMetadata("c:\\agentsky_audit.xls");
			System.out.println("end");
			
		}catch(Exception ex){
			ex.printStackTrace();
		}
	}
	
}